use sqlx::{PgExecutor, Postgres, QueryBuilder, Result};

use crate::{filter, model};

pub async fn find<'a>(
    e: impl PgExecutor<'a>,
    f: &filter::user_url::FindBy<'a>,
) -> Result<Option<model::user_url::UserUrl>> {
    let mut q = QueryBuilder::new(
        r#"SELECT "email", "id", "user_id", "origin", "url", "hit", "has_password", "password", "has_expired", "expired", "dateline" FROM "v_user_urls" WHERE 1=1"#,
    );

    match f {
        &filter::user_url::FindBy::ID(v) => q.push(r#" AND "id"= "#).push_bind(v),
        &filter::user_url::FindBy::URL(v) => q.push(r#" AND "url"= "#).push_bind(v),
        &filter::user_url::FindBy::UserID(v) => q.push(r#" AND "user_id"= "#).push_bind(v),
        &filter::user_url::FindBy::Email(v) => q.push(r#" AND "email"= "#).push_bind(v),
    };

    q.build_query_as().fetch_optional(e).await
}

pub async fn list_data<'a>(
    e: impl PgExecutor<'a>,
    f: &filter::user_url::List<'a>,
) -> Result<Vec<model::user_url::UserUrl>> {
    let mut q = QueryBuilder::new(
        r#"SELECT "email", "id", "user_id", "origin", "url", "hit", "has_password", "password", "has_expired", "expired", "dateline" FROM "v_user_urls" WHERE 1=1"#,
    );

    build_list_query(&mut q, f);

    let order = match f.order {
        Some(v) => v,
        None => "id DESC",
    };
    q.push(" ORDER BY ").push_bind(order);

    q.push(" LIMIT ")
        .push_bind(f.pagination.page_size())
        .push(" OFFSET ")
        .push_bind(f.pagination.offset());

    q.build_query_as().fetch_all(e).await
}

pub async fn list_count<'a>(e: impl PgExecutor<'a>, f: &filter::user_url::List<'a>) -> Result<u32> {
    let mut q = QueryBuilder::new(r#"SELECT COUNT(*) FROM "v_user_urls" WHERE 1=1"#);

    build_list_query(&mut q, f);

    let c: (i64,) = q.build_query_as().fetch_one(e).await?;

    Ok(c.0 as u32)
}

fn build_list_query<'a>(q: &mut QueryBuilder<Postgres>, f: &filter::user_url::List<'a>) {
    if let Some(v) = f.url {
        let p = format!("%{v}%");
        q.push(r#" AND "url" ILIKE "#).push_bind(p);
    }

    if let Some(v) = f.email {
        let p = format!("%{v}%");
        q.push(r#" AND "email" ILIKE "#).push_bind(p);
    }

    if let Some(v) = f.origin {
        let p = format!("%{v}%");
        q.push(r#" AND "origin" ILIKE "#).push_bind(p);
    }

    if let Some(v) = f.has_password {
        q.push(r#" AND "has_password" = "#).push_bind(v);
    }

    if let Some(v) = f.has_expired {
        q.push(r#" AND "has_expired" = "#).push_bind(v);
    }
    if let Some(v) = &f.expired_range {
        q.push(" AND (expired BETTEN ")
            .push_bind(v.start)
            .push(" AND ")
            .push_bind(v.end)
            .push(")");
    }
}
